<?php

namespace app\api\controller;

use app\common\controller\Api;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\App;
use think\Env;

/**
 * 示例接口
 */
class Demo extends Api
{

    //如果$noNeedLogin为空表示所有接口都需要登录才能请求
    //如果$noNeedRight为空表示所有接口都需要验证权限才能请求
    //如果接口已经设置无需登录,那也就无需鉴权了
    //
    // 无需登录的接口,*表示全部
    protected $noNeedLogin = ['test', 'test1', 'ahtumOrderSts'];
    // 无需鉴权的接口,*表示全部
    protected $noNeedRight = ['test2'];

    /**
     * 测试方法
     *
     * @ApiTitle    (测试名称)
     * @ApiSummary  (测试描述信息)
     * @ApiMethod   (POST)
     * @ApiRoute    (/api/demo/test/id/{id}/name/{name})
     * @ApiHeaders  (name=token, type=string, required=true, description="请求的Token")
     * @ApiParams   (name="id", type="integer", required=true, description="会员ID")
     * @ApiParams   (name="name", type="string", required=true, description="用户名")
     * @ApiParams   (name="data", type="object", sample="{'user_id':'int','user_name':'string','profile':{'email':'string','age':'integer'}}", description="扩展数据")
     * @ApiReturnParams   (name="code", type="integer", required=true, sample="0")
     * @ApiReturnParams   (name="msg", type="string", required=true, sample="返回成功")
     * @ApiReturnParams   (name="data", type="object", sample="{'user_id':'int','user_name':'string','profile':{'email':'string','age':'integer'}}", description="扩展数据返回")
     * @ApiReturn   ({
    'code':'1',
    'msg':'返回成功'
    })
     */
    public function test()
    {
        $this->success('返回成功', $this->request->param());
    }

    /**
     * 无需登录的接口
     *
     */
    public function test1()
    {
        $orderAmount = 1;
        $rate = 12;
        $tk_commission_fee = bcmul($orderAmount, bcdiv($rate, 100, 2), 2);//平台佣金
        $this->success('返回成功', ['action' => 'test1']);
    }

    /**
     * 需要登录的接口
     *
     */
    public function test2()
    {
        $this->success('返回成功', ['action' => 'test2']);
    }

    /**
     * 需要登录且需要验证有相应组的权限
     *
     */
    public function test3()
    {
        $this->success('返回成功', ['action' => 'test3']);
    }

    public function ahtumOrderSts()
    {

        $region = $this->request->get('region', 1);//vn=1，th=2,ml=3
        $shopFlag = $this->request->get('shopFlag', '');//A越南
        ini_set("memory_limit", "-1");

        //订单元数据
        $orderSheetsPath = 'C:\Users\91864\Desktop\Super Browser\C东元利华香港-美国跨境\C2ML-2025-03-21-16_53.xlsx';
        //结算单
        $orderSettlementSheetsPath = 'C:\Users\91864\Desktop\Super Browser\C东元利华香港-美国跨境\C2ML_income_20250321085313.xlsx';

        $spreadsheet = IOFactory::load($orderSheetsPath);
        //$spreadsheet->getSheet()
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
        $datas = [];
        for ($row = 3; $row <= $highestRow; ++$row) {
            $skuId = $worksheet->getCell('G' . $row)->getValue(); //SKUID
            if (empty($skuId[0]) || $skuId[0] != 'P') {
                continue;
            }
            $orderId = $worksheet->getCell('A' . $row)->getValue(); //订单ID
            $quantity = $worksheet->getCell('J' . $row)->getValue(); //数量
            $orderStatus = $worksheet->getCell('B' . $row)->getValue(); //订单状态
            $sellPrice = $worksheet->getCell('P' . $row)->getValue(); //商品卖价
            $orderAmount = $worksheet->getCell('W' . $row)->getValue(); //订单总额
            $createAt = $worksheet->getCell('Y' . $row)->getValue(); //创建时间
            $paidAt = $worksheet->getCell('Z' . $row)->getValue(); //支付时间
            $cancelledAt = $worksheet->getCell('AD' . $row)->getValue(); //取消时间
            $cancelReason = $worksheet->getCell('AF' . $row)->getValue(); //取消理由
            $warehouseNameO = $worksheet->getCell('AH' . $row)->getValue(); //仓库发货地址
            $warehouseName = strstr($warehouseNameO, 'Chinese') ? '国内' : $warehouseNameO;

            $deliveryType = $worksheet->getCell('AJ' . $row)->getValue(); //发货类型
            $datas[] = ['orderId' => strval($orderId),
                'orderStatus' => $orderStatus,
                'skuId' => $skuId,
                'quantity' => $quantity,
                'sellPrice' => $region == 1 ? intval(str_replace('.', '', $sellPrice)) : $sellPrice,//越南盾取整
                'orderAmount' => $region == 1 ? intval(str_replace('.', '', $orderAmount)) : $orderAmount,//越南盾取整
                'createAt' => $createAt,
                'paidAt' => $paidAt,
                'cancelledAt' => $cancelledAt,
                'cancelReason' => $cancelReason,
                'warehouseName' => $warehouseName,
                'deliveryType' => $deliveryType,
                'totalSettlementAmount' => '0',
                'sellerShippingFee' => '0',
                'transactionFee' => '0',
                'tkCommissionFee' => '0',
                'SPFServiceFee' => '0',
                'SSTFee' => '0',
                'BonusCashbackServiceFee' => '0',
                'VoucherXtraServiceFee' => '0',
                'AffiliateCommission' => '0',
            ];
        }

        $orderIndexByOrderId = array_column($datas, null, 'orderId');
        //var_dump($orderIndexByOrderId);

        //结算单元数据
        $settlementSheet = IOFactory::load($orderSettlementSheetsPath);
        $worksheetSettlement = $settlementSheet->getActiveSheet();
        $highestRowSettlement = $worksheetSettlement->getHighestRow(); // 总行数
        //$highestColumnSettlement = $worksheet->getHighestColumn(); // 总列数
        //var_dump($highestRowSettlement);die();
        for ($i = 2; $i <= $highestRowSettlement; ++$i) {
            if ($region == 1) {
                $orderIdS = strval($worksheetSettlement->getCell('AL' . $i)->getValue()); //订单ID
            } elseif ($region == 2 || $region == 3) {
                $orderIdS = strval($worksheetSettlement->getCell('E' . $i)->getValue()); //订单ID
            }else{
                continue;
            }
            //var_dump($orderIndexByOrderId);
            if (isset($orderIndexByOrderId[$orderIdS])) {
                $orderIndexByOrderId[$orderIdS]['totalSettlementAmount'] = $worksheetSettlement->getCell('J' . $i)->getValue(); //总结算金额
                //var_dump( $orderIndexByOrderId[$orderIdS]['totalSettlementAmount']);die();
                $orderIndexByOrderId[$orderIdS]['sellerShippingFee'] = $worksheetSettlement->getCell('U' . $i)->getValue(); //卖家实际支付运费
                $orderIndexByOrderId[$orderIdS]['transactionFee'] = $worksheetSettlement->getCell('S' . $i)->getValue(); //平台交易手续费
                $orderIndexByOrderId[$orderIdS]['tkCommissionFee'] = $worksheetSettlement->getCell('T' . $i)->getValue(); //平台佣金
                $orderIndexByOrderId[$orderIdS]['SPFServiceFee'] = $worksheetSettlement->getCell('AE' . $i)->getValue(); //SFP服务费
                $orderIndexByOrderId[$orderIdS]['SSTFee'] = $worksheetSettlement->getCell('AF' . $i)->getValue(); //增值税(SST)
                $orderIndexByOrderId[$orderIdS]['BonusCashbackServiceFee'] = $worksheetSettlement->getCell('AJ' . $i)->getValue(); //奖金返现服务费
                $orderIndexByOrderId[$orderIdS]['VoucherXtraServiceFee'] = $worksheetSettlement->getCell('AH' . $i)->getValue(); //超级优惠券服务费
                $orderIndexByOrderId[$orderIdS]['AffiliateCommission '] = $worksheetSettlement->getCell('AB' . $i)->getValue(); //联盟佣金
                /*                $orderIndexByOrderId[$orderIdS]['totalSettlementAmount'] = $totalSettlementAmount;//总结算金额
                                $orderIndexByOrderId[$orderIdS]['sellerShippingFee'] = $sellerShippingFee;//卖家实际支付运费
                                $orderIndexByOrderId[$orderIdS]['transactionFee'] = $transactionFee;//平台交易手续费
                                $orderIndexByOrderId[$orderIdS]['tkCommissionFee'] = $tkCommissionFee;*/
            }
        }


        //加载最终数据表格模板
        $orderSts = ROOT_PATH . 'public\uploads\sheettmps\settlementlist.xlsx';
        $orderStsSheet = IOFactory::load($orderSts);
        $worksheetWrite = $orderStsSheet->getActiveSheet();
        $from = 1;

        foreach ($orderIndexByOrderId as $key => $datum) {
            $from += 1;
            $worksheetWrite->getCell('A' . $from)->setValue($shopFlag);
            $worksheetWrite->getCell('B' . $from)->setValue("'" . $datum['orderId']);
            $worksheetWrite->getCell('C' . $from)->setValue($datum['skuId']);
            $worksheetWrite->getCell('G' . $from)->setValue($datum['quantity']);
            $worksheetWrite->getCell('E' . $from)->setValue($datum['orderStatus']);
            $worksheetWrite->getCell('F' . $from)->setValue($datum['sellPrice']);
            $worksheetWrite->getCell('H' . $from)->setValue($datum['orderAmount']);
            $worksheetWrite->getCell('I' . $from)->setValue($datum['createAt']);
            $worksheetWrite->getCell('J' . $from)->setValue($datum['warehouseName']);
            $worksheetWrite->getCell('K' . $from)->setValue($datum['cancelledAt']);
            $worksheetWrite->getCell('L' . $from)->setValue($datum['cancelReason']);
            $worksheetWrite->getCell('M' . $from)->setValue($datum['totalSettlementAmount']);
            $worksheetWrite->getCell('N' . $from)->setValue($datum['sellerShippingFee']);
            $worksheetWrite->getCell('O' . $from)->setValue($datum['transactionFee']);
            $worksheetWrite->getCell('P' . $from)->setValue($datum['tkCommissionFee']);
            $worksheetWrite->getCell('Q' . $from)->setValue($datum['SPFServiceFee']);
            $worksheetWrite->getCell('R' . $from)->setValue($datum['SSTFee']);
            $worksheetWrite->getCell('S' . $from)->setValue($datum['BonusCashbackServiceFee']);
            $worksheetWrite->getCell('T' . $from)->setValue($datum['VoucherXtraServiceFee']);
            $worksheetWrite->getCell('U' . $from)->setValue($datum['AffiliateCommission']);
        }

        $writer = IOFactory::createWriter($orderStsSheet, 'Xlsx');
        $filename = uniqid() . '.xlsx';
        $writer->save($filename);

        //$lines = $highestRow - 2;
//        if ($lines <= 0) {
//            exit('Excel表格中没有数据');
//        }


        $this->success('返回成功', ['action' => 'test3', 'file' => $filename]);
    }

}
